﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace ankietyzacja.Dao
{
    public class WyslanaAnkietaDao : Dao
    {


        public WyslanaAnkietaDao(SqlConnection c, SqlTransaction t)
            : base(c, t)
        {
        }

       

        public Int32 insert(Int32 emailId, Int32 ankietaId)
        {
            String sql =
                @"
INSERT INTO WYSLANAANKIETA
           (DATAWYSLANIA
           ,ANKIETA_ID
           ,EMAIL_ID)
     VALUES
           (null
           ,@ANKIETA_ID
           ,@EMAIL_ID)

";
            SqlCommand command = new SqlCommand(sql, connection, transaction);

            command.Parameters.Add(new SqlParameter("@ANKIETA_ID", ankietaId));
            command.Parameters.Add(new SqlParameter("@EMAIL_ID", emailId));


            return command.ExecuteNonQuery();

        }

        public Int32 updateDataWyslania(Int32 wyslanaAnkietaId)
        {

            String sql =
        @"  UPDATE WYSLANAANKIETA
           SET DATAWYSLANIA = GETDATE()
     WHERE WYSLANAANKIETA_ID = @WYSLANAANKIETA_ID;
     ";


            SqlCommand command = new SqlCommand(sql, connection, transaction);
            command.Parameters.Add(new SqlParameter("@WYSLANAANKIETA_ID", wyslanaAnkietaId));

            return command.ExecuteNonQuery();

        }




        public DataTable loadForEmail(Int32 emailId)
        {

            String sql =
        @" SELECT w.*, a.NAZWAANKIETY
             FROM WYSLANAANKIETA w, ANKIETA a
     WHERE w.ANKIETA_ID = a.ANKIETA_ID
       AND w.EMAIL_ID = @EMAIL_ID;
     ";
            SqlCommand command = new SqlCommand(sql, connection, transaction);
            command.Parameters.Add(new SqlParameter("@EMAIL_ID", emailId));


            DataTable dataTable = new DataTable();

            new SqlDataAdapter(command).Fill(dataTable);

            return dataTable;

        }


        public Int32 insertUzytkownik(Int32 wyslanaAnkietaId, String uzytkownikId)
        {
            String sql =
                @"
INSERT INTO WYSLANAANKIETA_x_UZYTKOWNIK
           (WYSLANAANKIETA_ID
           ,UZYTKOWNIK_ID)
     VALUES
           (@WYSLANAANKIETA_ID
           ,@UZYTKOWNIK_ID)

";
            SqlCommand command = new SqlCommand(sql, connection, transaction);

            command.Parameters.Add(new SqlParameter("@WYSLANAANKIETA_ID", wyslanaAnkietaId));
            command.Parameters.Add(new SqlParameter("@UZYTKOWNIK_ID", uzytkownikId));


            return command.ExecuteNonQuery();

        }


        public DataTable loadDaneAnkiety(Int32 ankietaId)
        {

            String sql = @"
            SELECT  
            (SELECT COUNT(1) 
              FROM WYSLANAANKIETA w, WYSLANAANKIETA_x_UZYTKOWNIK wu
             WHERE w.WYSLANAANKIETA_ID = wu.WYSLANAANKIETA_ID
               AND w.ANKIETA_ID = @ANKIETA_ID) WYSLANYCH,
            (SELECT COUNT(1) 
              FROM WYPELNIONAANKIETA w
             WHERE w.ANKIETA_ID = @ANKIETA_ID) WYPELNIONYCH";

            SqlCommand command = new SqlCommand(sql, connection, transaction);
            command.Parameters.Add(new SqlParameter("@ANKIETA_ID", ankietaId));

            DataTable dataTable = new DataTable();

            new SqlDataAdapter(command).Fill(dataTable);

            return dataTable;


        }

        
    }


}